Create Database SQL2008CDC
Go


Use SQL2008CDC
Go


EXEC sp_cdc_enable_db_change_data_capture
GO


Select [name] as DBName,is_cdc_enabled from sys.databases


CREATE TABLE dbo.Employee(
EmpID int NOT NULL primary key,
EmpName nvarchar(100) NOT NULL,
EmpEmail nvarchar(100) NOT NULL)
Go


EXEC sp_cdc_enable_table_change_data_capture 
    @source_schema = N'dbo'
  , @source_name = N'Employee'
  , @role_name = N'cdc_admin'
  , @capture_instance = N'dbo_Employee' 
  , @supports_net_changes = 1;


select [name], is_tracked_by_cdc from sys.tables



INSERT INTO dbo.Employee values (1, N'George Clooney', N'George.clooney@oceans13.com')
INSERT INTO dbo.Employee values (2, N'Ben Stiller', N'Ben.Stiller@oceans13.com')
INSERT INTO dbo.Employee values (3, N'Tom Cruise', N'Tom.Cruise@oceans13.com')
INSERT INTO dbo.Employee values (4, N'Julia Roberts', N'Julia.Roberts@oceans13.com')

select * from dbo.Employee
select * from cdc.dbo_employee_ct

Delete from dbo.Employee where EmpId=4


Update dbo.Employee Set EmpName='Brad Pitt', EmpEmail='Brad.Pitt@Oceans13.com' where EmpId = 2
Update dbo.Employee Set EmpName='Matt Damon', EmpEmail='Matt.Damon@Oceans13.com' where EmpId = 3


DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);
SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
-- Map the time interval to a change data capture query range.
SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

select @begin_time, @end_time , @from_lsn , @to_lsn;
-- Return the changes occurring within the query window.
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Employee(@from_lsn, @to_lsn, 'all');

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Employee(@from_lsn, @to_lsn,'all')

GO 